﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using K3TOOLS.Models;

namespace K3TOOLS.Controllers
{
    public class HomeController : Controller
    {
        private readonly BloggingContext _context;
        public HomeController(BloggingContext context)
        {
            _context = context;
        }


        public IActionResult Index()
        {
            return View();
        }

        public IActionResult About()
        {
            ViewData["Message"] = "Your application description page.";

            return View();
        }

        public IActionResult Contact()
        {
            ViewData["Message"] = "Your contact page.";

            return View();
        }

        public IActionResult Error()
        {
            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
        }


        public IActionResult Test()
        {

            var list = _context.apitest1.ToList();
            bool result = true;

            return View(list);
        }


        public IActionResult Kindee()
        {
            return View();
        }

        public IActionResult GetK3DB(string TbInfo = "")
        {
            List<TableInfo> TableInfoList = GetData(TbInfo);

            return View(TableInfoList);
        }

        private List<TableInfo> GetData(string TbInfo = "")
        {
            List<TableInfo> TableInfoList = new List<TableInfo>();
            string Sql = string.Format(@"
                    select REPLACE(REPLACE(REPLACE(t.item, '<TableName>', ''),'</TableName>',','),'<TableName/>','') as TableList,
                    t.FNAME as TableDescription from
                    (
                    select convert(varchar(8000),t1.FKERNELXML.query('//TableName')) as  'Item',t1.FKERNELXML xx,t2.FNAME, t1.* 
                    from T_META_OBJECTTYPE t1 
                    left join T_META_OBJECTTYPE_L t2 on t1.FID=t2.FID
                    --where t1.fid='SAL_SaleOrder'
                    ) t
                    where t.Item<>''  and t.FNAME like '%{0}%'
                ", TbInfo);
            try
            {
                TableInfoList = _context.TableInfo.
                FromSql(Sql).ToList();
            }
            catch (Exception ex)
            {
                string error = ex.ToString();
                TableInfoList = null;
            }
            return TableInfoList;
        }

        public IActionResult TableDetail(string TableName = "")
        {
            List<TableColumnData> TableColums = GetTableColumns(TableName);
            //List<dynamic> MainTableList = GetMainTableList(TableName);
            List<dynamic> LanguageTableList = GetLanguageTableList(TableName);
            //ViewBag.MTBlist = MainTableList;
            //ViewBag.LTList = LanguageTableList;
            return View(TableColums);
        }

        public List<TableColumnData> GetTableColumns(string TableName = "")
        {
            List<TableColumnData> result = new List<TableColumnData>();
            string Sql = string.Format(@"
                SELECT
	                syscolumns.name as ColumnName,
	                systypes.name as DataType,
	                cast (syscolumns.isnullable as varchar) as ColIsNullable,
	                cast(syscolumns.length as varchar) as ColLength
                FROM
	                syscolumns,
	                systypes
                WHERE
	                syscolumns.xusertype = systypes.xusertype
                AND syscolumns.id = object_id('{0}')
            ", TableName);
            try
            {
                result = _context.TableColumnData
                    .FromSql(Sql).ToList();
            }
            catch (Exception ex)
            {

            }
            
            return result;
        }

        public List<object> GetMainTableList(string TableName = "")
        {
            List<object> result = new List<object>();
            //string Sql = string.Format(@"
            //    SELECT
	           //     TOP 10 *
            //    FROM {0}
            //", TableName);
            //try
            //{
            //    result = _context.DyTable
            //        .FromSql(Sql).ToList();
            //}
            //catch (Exception ex)
            //{

            //}

            return result;
        }

        public List<dynamic> GetLanguageTableList(string TableName = "")
        {
            List<dynamic> result = new List<dynamic>();
            TableName = TableName + "_L";
            string IsExistSql = string.Format(@"
                IF EXISTS (
	                SELECT
		                *
	                FROM
		                dbo.SysObjects
	                WHERE
		                ID = object_id(N'[{0}]')
	                AND OBJECTPROPERTY(ID, 'IsTable') = 1
                ) PRINT '1'
                ELSE
	                PRINT '0'
            ", TableName);



            string Sql = string.Format(@"
                SELECT
	                TOP 10 *
                FROM {0}
            ", TableName);
            try
            {
                var exist = _context.Set<dynamic>().FromSql(IsExistSql).ToList();
                result = _context.Set<dynamic>().FromSql(Sql).ToList();
                //if (exist.ToString() == "1")
                //{
                //    result = _context.Set<dynamic>().FromSql(Sql).ToList();
                //}
                //else
                //{
                //    result = new List<object>();
                //}


            }
            catch (Exception ex)
            {

            }

            return result;
        }

    }
}
